﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Entidad;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace Dao
{
    public class AlumnoDao
    {
        public static SqlCommand cmd;
        private static SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["AcademicoConnectionString"].ConnectionString);
        public AlumnoDao()
        {
          
        }
        public static List<Alumno> ObtenerTodo()
        {
            List<Alumno> listAlumno = new List<Alumno>();
            string sql = "select * from Alumno";

            try
            {
                cn.Open();
                SqlCommand cmd = new SqlCommand(sql, cn);
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    Alumno alu = new Alumno();
                    alu.legajo = (int)dr["legajo"];
                    alu.nombre = dr["nombre"].ToString();
                    alu.apellido = dr["apellido"].ToString();
                    alu.barrio = dr["barrio"].ToString();
                    alu.cp = (int)dr["cp"];
                    alu.depto = dr["depto"].ToString();
                    alu.direccion = dr["direccion"].ToString();
                    alu.numCalle = (int)dr["numCalle"];
                    alu.pass = dr["pass"].ToString();
                    alu.telefono = int.Parse(dr["telefono"].ToString());
                    alu.estadoAcademico = (bool)dr["estadoAcademico"];
                    alu.codCarrera = (int)dr["codCarrera"];
                    alu.codProvincia = (int)dr["codProvincia"];
                    alu.dni = (int)dr["dni"];

                    listAlumno.Add(alu);
                }
                dr.Close();
                cn.Close();
            }
            catch (SqlException ex)
            {
                if (cn.State == ConnectionState.Open)
                    cn.Close();
                throw new ApplicationException("Error BD Alumno \n Problema:" + ex.Message);
            }
            return listAlumno;

        }
        public static List<Alumno> ObtenerTodoGrilla()
        {
            List<Alumno> listAlumno = new List<Alumno>();
            string sql = @"Select legajo, a.apellido + ', ' + a.nombre as 'Apellido y Nombre', dni as 'D.N.I.' , c.nombre as Carrera, direccion + ' ' + CAST(numCalle as varchar)+ ' Dpto:' + depto + ' Bº ' + barrio  as Domicilio, 
                        cp as 'Codigo Postal' , p.nombre as Provincia, estadoAcademico as Matriculado 
                        from Alumno a inner join Provincia p on a.codProvincia=p.codProvincia inner join Carrera c on a.codCarrera = c.codCarrera
                        Order by a.apellido, a.nombre";
            
            try
            {
                cn.Open();
                SqlCommand cmd = new SqlCommand(sql, cn);
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    Alumno alu = new Alumno();
                    alu.legajo = (int)dr["legajo"];
                    alu.nombre = dr[1].ToString();
                    alu.dni = (int)dr[2];
                    alu.carrera = dr[3].ToString();
                    alu.direccion = dr[4].ToString();
                    alu.cp = (int)dr[5];
                    alu.provincia = dr[6].ToString();
                    alu.estadoAcademico = (bool)dr[7];
                    listAlumno.Add(alu);
                }
                dr.Close();
                cn.Close();
            }
            catch (SqlException ex)
            {
                if (cn.State == ConnectionState.Open)
                    cn.Close();
                throw new ApplicationException("Error BD Alumno \n Problema:" + ex.Message);
            }
            return listAlumno;

        }

        public static Alumno ObtenerPorId(int legajo)
        {
            Alumno alu = new Alumno();
            string sql = "select * from alumno where legajo = @Legajo";
            try
            {
                cn.Open();
                cmd = new SqlCommand(sql, cn);
                cmd.Parameters.AddWithValue("@Legajo", legajo);
                SqlDataReader dr = cmd.ExecuteReader();
                if (dr.Read())
                {
                    alu.legajo = (int)dr["legajo"];
                    alu.nombre = dr["nombre"].ToString();
                    alu.apellido = dr["apellido"].ToString();
                    alu.barrio = dr["barrio"].ToString();
                    alu.cp = (int)dr["cp"];
                    alu.depto = dr["depto"].ToString();
                    alu.direccion = dr["direccion"].ToString();
                    alu.numCalle = (int)dr["numCalle"];
                    alu.pass = dr["pass"].ToString();
                    alu.telefono = Int64.Parse(dr["telefono"].ToString());
                    alu.estadoAcademico = bool.Parse(dr["estadoAcademico"].ToString());
                    alu.codCarrera = (int)dr["codCarrera"];
                    alu.codProvincia = (int)dr["codProvincia"];
                    alu.dni = (int)dr["dni"];
                }
                dr.Close();
                cn.Close();
            }
            catch (SqlException ex)
            {
                if (cn.State == ConnectionState.Open)
                    cn.Close();
                throw new ApplicationException("Error BD Alumno \n Problema:" + ex.Message);
            }
            return alu;

        }

        public static void Insertar(Alumno alu)
        {
            string sql = @"insert into alumno (apellido, nombre, direccion, numCalle, depto, barrio, telefono, codCarrera, cp, estadoAcademico, codProvincia, pass, dni) 
         values (@apellido,@nombre,@direccion,@numCalle,@depto,@barrio,@telefono,@codCarrera,@cp,@estadoAcademico,@codProvincia,@pass,@dni); select @@Identity as legajo;";
                       
            try
            {
                cn.Open();
                cmd = new SqlCommand(sql, cn);
                cmd.Parameters.Add("@nombre", SqlDbType.VarChar,20).Value= alu.nombre;
                cmd.Parameters.Add("@apellido", SqlDbType.VarChar,20).Value= alu.apellido;
                cmd.Parameters.Add("@barrio", SqlDbType.VarChar,20).Value=  alu.barrio;
                cmd.Parameters.Add("@cp", SqlDbType.Int).Value= alu.cp;
                cmd.Parameters.Add("@depto", SqlDbType.Char, 5).Value= alu.depto;
                cmd.Parameters.Add("@direccion", SqlDbType.VarChar,30).Value= alu.direccion;
                cmd.Parameters.Add("@numCalle", SqlDbType.Int).Value= alu.numCalle;
                cmd.Parameters.Add("@pass", SqlDbType.VarChar,50).Value= alu.pass;
                cmd.Parameters.Add("@telefono", SqlDbType.BigInt).Value= alu.telefono;
                cmd.Parameters.Add("@estadoAcademico", SqlDbType.Bit).Value= alu.estadoAcademico;
                cmd.Parameters.Add("@codCarrera", SqlDbType.Int).Value= alu.codCarrera;
                cmd.Parameters.Add("@codProvincia", SqlDbType.Int).Value= alu.codProvincia;
                cmd.Parameters.Add("@dni", SqlDbType.Int).Value= alu.dni;
            
                alu.legajo = Convert.ToInt32(cmd.ExecuteScalar());
                cn.Close();
            }
            catch (SqlException ex)
            {
                if (cn.State == ConnectionState.Open)
                    cn.Close();
                throw new ApplicationException("Error BD Alumno\n Problema:" + ex.Message);
            }
        }

        public static void Actualizar(Alumno alu)
        {

            string sql = @"update alumno set nombre = @nombre, apellido = @apellido, barrio = @barrio, cp = @cp, depto = @depto, 
                                             direccion = @direccion, numCalle = @numCalle, pass = @pass, telefono = @telefono,
                                             estadoAcademico = @estadoAcademico, codCarrera = @codCarrera, codProvincia = @codProvincia,
                                             dni = @Dni  WHERE legajo = @Legajo";
            try
            {
                cn.Open();
                cmd = new SqlCommand(sql, cn);
                cmd.Parameters.Add("@Legajo", SqlDbType.Int).Value= alu.legajo;
                cmd.Parameters.Add("@nombre", SqlDbType.VarChar, 20).Value = alu.nombre;
                cmd.Parameters.Add("@apellido", SqlDbType.VarChar, 20).Value = alu.apellido;
                cmd.Parameters.Add("@barrio", SqlDbType.VarChar, 20).Value = alu.barrio;
                cmd.Parameters.Add("@cp", SqlDbType.Int).Value = alu.cp;
                cmd.Parameters.Add("@depto", SqlDbType.Char, 5).Value = alu.depto;
                cmd.Parameters.Add("@direccion", SqlDbType.VarChar, 30).Value = alu.direccion;
                cmd.Parameters.Add("@numCalle", SqlDbType.Int).Value = alu.numCalle;
                cmd.Parameters.Add("@pass", SqlDbType.VarChar, 50).Value = alu.pass;
                cmd.Parameters.Add("@telefono", SqlDbType.BigInt).Value = alu.telefono;
                cmd.Parameters.Add("@estadoAcademico", SqlDbType.Bit).Value = alu.estadoAcademico;
                cmd.Parameters.Add("@codCarrera", SqlDbType.Int).Value = alu.codCarrera;
                cmd.Parameters.Add("@codProvincia", SqlDbType.Int).Value = alu.codProvincia;
                cmd.Parameters.Add("@dni", SqlDbType.Int).Value = alu.dni;
               
                cmd.ExecuteNonQuery();
                cn.Close();
            }
            catch (SqlException ex)
            {
                if (cn.State == ConnectionState.Open)
                    cn.Close();
                throw new ApplicationException("Error BD Alumno \n Problema:" +ex.Message);
            }
        }

        public static void Eliminar(int legajo)
        {

            string sql = @"delete from alumno
                            WHERE legajo = @Legajo";
            try
            {
                cn.Open();
                cmd = new SqlCommand(sql, cn);
                cmd.Parameters.AddWithValue("@Legajo", legajo);
                cmd.ExecuteNonQuery();
                cn.Close();
            }
            catch (SqlException ex)
            {
                if (cn.State == ConnectionState.Open)
                    cn.Close();
                throw new ApplicationException("Error BD Alumno \n Problema:" + ex.Message);
            }

        }

        public static bool ValidarDNI(int dni)
        {
            string sql = "select * from alumno where dni = @Dni";
            cmd = new SqlCommand(sql, cn);
                cmd.Parameters.AddWithValue("@Dni", dni);
                bool valido = true;
            try
            {
                cn.Open();
                SqlDataReader dr = cmd.ExecuteReader();
                    while (dr.Read())
                    {
                        valido = false;
                    }
                dr.Close();
                cn.Close();
            }
            catch (SqlException ex)
            {
                if (cn.State == ConnectionState.Open)
                    cn.Close();
                throw new ApplicationException("Error BD Alumno \n Problema:" + ex.Message);
            }
            return valido;

        }
    
    }
}
